use [gd1c2011]
GO


IF OBJECT_ID ('LAG.clientesPremium','P') IS NOT NULL
    DROP PROCEDURE LAG.clientesPremium ;
GO
CREATE PROCEDURE LAG.clientesPremium
	@arg_Ano int , 
	@arg_idSucursal int 
AS
BEGIN
	SELECT TOP 30
		per.nombre as nombre , 
		per.apellido as apellido , 
		per.dni as dni , 
		sum(fac.total) as total , 
		(	
			SELECT sum( fit.cantidad )
			FROM LAG.facturas_items fit 
			WHERE fit.nroFactura IN (
			 		SELECT fac2.nroFactura 	FROM LAG.facturas fac2
					WHERE fac2.idCliente = fac.idCliente AND year( fac2.fecha ) = @arg_Ano AND fac2.idSucursal = @arg_idSucursal
				)
		) as cantProd ,
		max( fac.fecha ) as ultimaCompra ,
		(
			SELECT TOP 1 per3.dni
			FROM LAG.usuarios usu3
			JOIN LAG.personas per3 ON per3.idPersona = usu3.idPersona
			WHERE usu3.idUsuario = ( -- Obtenemos el idUsuario , de la ultima factura vendida a ese cliente en ese a~no en esa sucursal.
					SELECT TOP 1 fac3.idUsuario	
					FROM LAG.facturas fac3 
					WHERE fac3.idCliente = fac.idCliente AND year( fac3.fecha ) = @arg_Ano AND fac3.idSucursal = @arg_idSucursal 
					ORDER BY fac3.nroFactura DESC 
				)
		) as dniUltimoVendedor
	FROM LAG.facturas fac
	JOIN LAG.personas per ON per.idPersona = fac.idCliente
	WHERE year( fac.fecha ) = @arg_Ano AND fac.idSucursal = @arg_idSucursal
	GROUP BY fac.idCliente , per.nombre , per.apellido , per.dni 
	ORDER BY sum(fac.total) DESC ;
END
GO




EXEC LAG.clientesPremium 2005,2
GO
